package esController

import (
	"EsTag/esUtility"
	//	"EsTag/mysqlUtility"
	dsql "database/sql"
	"fmt"
	"log"
	//	"sync"
	Antmongo "EsTag/antmongo"
	"EsTag/mysqlUtility"
	"strings"

	"github.com/astaxie/beego"
)

//定时任务-删除索引
func DeleteTagIndex(indexstr string) {

	fmt.Println("删除DeleteIndexByName===========begin============" + indexstr)

	esUtility.DeleteIndexByName(indexstr)

	fmt.Println("删除DeleteIndexByName===========end==============" + indexstr)

}

func CreateTagIndexAndStartTransport(indexstr string) {
	fmt.Println("导入user表==============begin==============")

	fmt.Println("新增标签索引CreateTagIndexAndStartTransport===========" + indexstr)

	esUtility.CreateTagIndex(indexstr)

	fmt.Println("循环数据库begin===========" + indexstr)
	// 获取学校列表
	var schoolTotal string = beego.AppConfig.String("todo-school::name")
	var schoolList = strings.Split(schoolTotal, ".")
	fmt.Println(schoolList)

	// 遍历，删除并创建索引，插入数据
	for _, school := range schoolList {
		fmt.Println(school)
		var dbConfig = mysqlUtility.DbConfig{
			IP:        beego.AppConfig.String("mysql-" + school + "::host"),
			Port:      beego.AppConfig.DefaultInt64("mysql-"+school+"::port", 3306),
			User:      beego.AppConfig.String("mysql-" + school + "::user"),
			Pwd:       beego.AppConfig.String("mysql-" + school + "::pwd"),
			Database:  beego.AppConfig.String("mysql-" + school + "::database"),
			IdleConns: beego.AppConfig.DefaultInt("mysql-"+school+"::idleconns", 100),
		}

		// 数据库初始化
		mysqlUtility.DB_CONF = dbConfig
		mysqlUtility.StartInit()

		TransportTagMySqlToEs(indexstr, school)
		mysqlUtility.CloseConn()
	}

}

// 定时任务
func TransportTagMySqlToEs(indexstr string, school string) {
	fmt.Println("==================TransportTagMySqlToEs标签索引")
	//	//涉及表  user  studentbaseinfo 宿舍 学校 学院 班级 年级 专业
	var (
		id     int64
		limit  = 1000
		pageno = 0
	)

	for {
		users, err := QueryTagUserData(id, limit, school)
		if err != nil {
			fmt.Println("查询用户数据错误：", err.Error())
			return
		} else if len(users) == 0 {
			break
		}
		//		var usarr []*esUtility.TagUser
		log.Println("查询用户数据：", id, " - ", len(users))
		//		if id > 120594 {
		//			break
		//		}
		var tagUsers []interface{}
		for i, user := range users {
			if i == len(users)-1 {
				id = user.UId
			}
			var us esUtility.TagUser
			us.Xg = user
			//			usarr = append(usarr, &us)
			tagUsers = append(tagUsers, &us)
		}
		//		err = esUtility.InsertTagMultiDocsToES(indexstr, "tagUser", usarr...)

		err = esUtility.InsertTagMultiDocsToES(indexstr, "tagUser", tagUsers...)
		if err != nil {
			fmt.Println("插入ES数据发生错误：", err.Error())
			return
		}
		log.Println("学工系统数据库数据写入成功：", id)
	}
	//查询mongodb数据库
	sartNo := pageno * limit
	Antmongo.MgoLoadConfig()
	list := Antmongo.QueryJjhUsers(sartNo, limit)
	err := esUtility.InsertTagMultiMongodb(indexstr, "tagUser", list)
	if err != nil {
		fmt.Println("mongodb插入ES数据发生错误：", err.Error())
		return
	}
	fmt.Println("mongodb数据库数据写入成功：", sartNo)
	for {
		sartNo = sartNo + limit + 1
		list = Antmongo.QueryJjhUsers(sartNo, limit)
		if list == nil {
			break
		}
		err = esUtility.InsertTagMultiMongodb(indexstr, "tagUser", list)
		if err != nil {
			fmt.Println("mongodb插入ES数据发生错误：", err.Error())
			return
		}
		fmt.Println("mongodb数据库数据写入成功：", sartNo)
	}
	fmt.Println(list)
	fmt.Println("集结号系统数据库数据写入成功=============end==============" + indexstr)
	fmt.Println("导入user表==============end==============" + indexstr)
}

// 封装数据库查询
func queryTagRows(sql string, cb func(rows *dsql.Rows) error, values ...interface{}) {
	rows, err := mysqlUtility.DBConn.Query(sql, values...)
	fmt.Println("========================gggggggggggggggggg==========")
	if err != nil {
		fmt.Println("数据查询出错 真的", err.Error())
		return
	}

	defer rows.Close()

	for rows.Next() {
		err = cb(rows)
		if err != nil {
			return
		}
	}
}

// 查询用户数据
func QueryTagUserData(id int64, limit int, school string) (users []*esUtility.XgModel, err error) {
	sql := `
		SELECT 
		uId,
		u.IntelUserCode,
		uUserCode,
		uName,
		uSex,
		uSexName,
		uUserType,
		uUserTypeName,
		uEnrollmentStatus,
		EnrollmentStatusName,
		uType,
		uTypeName,
		uPhone,
		uEmail,
		  COALESCE(sbi.OtherName, "") AS sbiOtherName,
		  COALESCE(sbi.NameInPinyin, "") AS sbiNameInPinyin,
		  COALESCE(sbi.EnglishName, "") AS sbiEnglishName,
		  COALESCE(sbi.CountryCode, "") AS sbiCountryCode,
		  bizName (COALESCE(sbi.CountryCode, "")) AS sbiCountryCodeName,
		  COALESCE(sbi.NationalityCode, "") AS sbiNationalityCode,
		  bizName (COALESCE(sbi.NationalityCode, "")) AS sbiNationalityCodeName,
		  COALESCE(sbi.Birthday, "") AS sbiBirthday,
		  COALESCE(sbi.PoliticalCode, "") AS sbiPoliticalCode,
		  bizName (COALESCE(sbi.PoliticalCode, "")) AS sbiPoliticalCodeName,
		  COALESCE(sbi.QQAcct, "") AS sbiQQAcct,
		  COALESCE(sbi.AccountBankCode, "") AS sbiAccountBankCode,
		  COALESCE(sbi.MaritalCode, "") AS sbiMaritalCode,
		  bizName (COALESCE(sbi.MaritalCode, "")) AS MaritalCodeName,
		  COALESCE(sbi.OriginAreaCode, "") AS sbiOriginAreaCode,
		  CONCAT(
		    geography (1, sbi.OriginAreaCode),
		    geography (2, sbi.OriginAreaCode),
		    geography (3, sbi.OriginAreaCode)
		  ) AS OriginAreaCodeName,
		  COALESCE(sbi.StudentAreaCode, "") AS sbiStudentAreaCode,
		  CONCAT(
		    CONCAT(
		      SUBSTRING(sbi.StudentAreaCode, 1, 2),
		      '0000'
		    ),
		    '    ',
		    CONCAT(
		      SUBSTRING(sbi.StudentAreaCode, 1, 4),
		      '00'
		    ),
		    '    ',
		    sbi.StudentAreaCode
		  ) AS sbiStudentAreaCodeAll,
		  CONCAT(
		    geography (1, sbi.StudentAreaCode),
		    geography (2, sbi.StudentAreaCode),
		    geography (3, sbi.StudentAreaCode)
		  ) AS SbiStudentAreaCodeName,
		  COALESCE(sbi.Hobbies, "") AS sbiHobbies,
		  COALESCE(sbi.FamilyAddress, "") AS sbiFamilyAddress,
		  CONCAT(
		    geography (1, sbi.FamilyAddress),
		    geography (2, sbi.FamilyAddress),
		    geography (3, sbi.FamilyAddress)
		  ) AS sbiFamilyAddressName,
		  COALESCE(sbi.DetailAddress, "") AS sbiDetailAddress,
		  COALESCE(sbi.PostCode, "") AS sbiPostCode,
		  COALESCE(sbi.HomePhone, "") AS sbiHomePhone,
		  COALESCE(sbi.EnrollmentDate, "") AS sbiEnrollmentDate,
		  COALESCE(sbi.GraduationDate, "") AS sbiGraduationDate,
		  COALESCE(sbi.MidSchoolAddress, "") AS sbiMidSchoolAddress,
		  geography (1, sbi.MidSchoolAddress) AS sbiMidSchoolAddressName,
		  COALESCE(sbi.MidSchoolName, "") AS sbiMidSchoolName,
		  COALESCE(
		    sbi.CollegeEntranceExamScores,
		    ""
		  ) AS sbiCollegeEntranceExamScores,
		  COALESCE(sbi.AdmissionYear, "") AS sbiAdmissionYear,
		  bizName (COALESCE(sbi.AdmissionYear, "")) AS sbiAdmissionYearName,
		  COALESCE(sbi.ForeignLanguageCode, "") AS sbiForeignLanguageCode,
		  bizName (
		    COALESCE(sbi.ForeignLanguageCode, "")
		  ) AS sbiForeignLanguageCodeName,
		  COALESCE(sbi.StudentOrigin, "") AS sbiStudentOrigin,
		  bizName (COALESCE(sbi.StudentOrigin, "")) AS sbiStudentOriginName,
		  COALESCE(cl.ClassCode, "") AS clClassCode,
		  COALESCE(cl.ClassName, "") AS clClassName,
		  COALESCE(cl.ResearchArea, "") AS clResearchArea,
		  COALESCE(cl.TrainingMode, "") AS clTrainingMode,
		  bizName (COALESCE(cl.TrainingMode, "")) AS clTrainingModeName,
		  COALESCE(cl.ProgramLength, "") AS clProgramLength,
		  bizName (COALESCE(cl.ProgramLength, "")) AS clProgramLengthName,
		  COALESCE(cl.StudentType, "") AS clStudentType,
		  bizName (COALESCE(cl.StudentType, "")) AS clStudentTypeName,
		  COALESCE(cl.CredentialsType, "") AS clCredentialsType,
		  bizName (COALESCE(cl.CredentialsType, "")) AS clCredentialsTypeName,
		  COALESCE(cl.DegreeType, "") AS clDegreeType,
		  bizName (COALESCE(cl.DegreeType, "")) AS clDegreeTypeName,
		  COALESCE(cl.Counselor, "") AS clCounselor,
		  getUserName (cl.Counselor) AS clCounselorName,
		  COALESCE(cl.Adviser, "") AS clAdviser,
		  getUserName (cl.Adviser) AS clAdviserName,
		  COALESCE(cl.Leadership, "") AS clLeadership,
		  getUserName (cl.Leadership) AS clLeadershipName,
		  COALESCE(cl.Supervisor, "") AS clSupervisor,
		  getUserName (cl.Supervisor) AS clSupervisorName,
		  COALESCE(cp.CampusName, "") AS cpCampusName,
		  COALESCE(cp.CampusCode, "") AS cpCampusCode,
		  COALESCE(gd.GradeName, "") AS gdGradeName,
		  COALESCE(gd.GradeCode, "") AS gdGradeCode,
		  COALESCE(ac.AcademyName, "") AS acAcademyName,
		  COALESCE(ac.AcademyCode, "") AS acAcademyCode,
		  COALESCE(mj.MajorName, "") AS mjMajorName,
		  COALESCE(mj.MajorCode, "") AS mjMajorCode 
		FROM
		 ( SELECT 
		 
		 t.Id AS uId,
		  COALESCE(t.IntelUserCode, "") AS IntelUserCode,
		  COALESCE(t.UserCode, "") AS uUserCode,
		  COALESCE(t.Name, "") AS uName,
		  COALESCE(t.Sex, "") AS uSex,
		  bizName (COALESCE(t.Sex, "")) AS uSexName,
		  COALESCE(t.UserType, "") AS uUserType,
		  bizName (COALESCE(t.UserType, "")) AS uUserTypeName,
		  COALESCE(t.EnrollmentStatus, "") AS uEnrollmentStatus,
		  bizName (COALESCE(t.EnrollmentStatus, "")) AS EnrollmentStatusName,
		  COALESCE(t.Type, "") AS uType ,
		  CASE
		    t.Type 
		    WHEN '1' 
		    THEN '学工' 
		    WHEN '2' 
		    THEN '学生' 
		    WHEN '0' 
		    THEN '系统管理员' 
		    WHEN '9' 
		    THEN '测试用户' 
		    ELSE '' 
		  END AS uTypeName,
		  COALESCE(t.Phone, "") AS uPhone,
		  COALESCE(t.Email, "") AS uEmail 
		 
		   FROM  USER  t  
`
	if id > 0 {
		sql = fmt.Sprintf("%s where t.Id>%d", sql, id)
	}

	sql = fmt.Sprintf("%s order by t.id  limit %d", sql, limit)

	sql = sql + ` ) u 
  LEFT JOIN StudentBasicInfo sbi 
    ON sbi.IntelUserCode = u.IntelUserCode 
    AND sbi.Status = 1 
  LEFT JOIN Class cl 
    ON cl.ClassCode = sbi.class 
  LEFT JOIN Campus cp 
    ON cp.CampusCode = cl.Campus 
  LEFT JOIN Grade gd 
    ON gd.GradeCode = cl.Grade 
  LEFT JOIN Academy ac 
    ON ac.AcademyCode = 
    (SELECT 
      t1.ParentDepartCode 
    FROM
      Organization t1 
    WHERE t1.DepartmentCode = 
      (SELECT 
        t3.ParentDepartCode 
      FROM
        Organization t3 
      WHERE t3.DepartmentCode = sbi.class)) 
  LEFT JOIN Major mj 
    ON mj.MajorCode = 
    (SELECT 
      t3.ParentDepartCode 
    FROM
      Organization t3 
    WHERE t3.DepartmentCode = sbi.class) 				 
	
	 `

	//	fmt.Println("=======================sql============================")
	//	//	fmt.Println(sql)
	//	fmt.Println(sql)
	queryTagRows(sql, func(rows *dsql.Rows) (err error) {
		var user esUtility.XgModel
		scanErr := rows.Scan(
			&user.UId,
			&user.IntelUserCode,
			&user.UUserCode,
			&user.UName,
			&user.USex,
			&user.USexName,
			&user.UUserType,
			&user.UUserTypeName,
			&user.UEnrollmentStatus,
			&user.EnrollmentStatusName,
			&user.UType,
			&user.UTypeName,
			&user.UPhone,
			&user.UEmail,
			&user.SbiOtherName,
			&user.SbiNameInPinyin,
			&user.SbiEnglishName,
			&user.SbiCountryCode,
			&user.SbiCountryCodeName,
			&user.SbiNationalityCode,
			&user.SbiNationalityCodeName,
			&user.SbiBirthday,
			&user.SbiPoliticalCode,
			&user.SbiPoliticalCodeName,
			&user.SbiQQAcct,
			&user.SbiAccountBankCode,
			&user.SbiMaritalCode,
			&user.MaritalCodeName,
			&user.SbiOriginAreaCode,
			&user.OriginAreaCodeName,
			&user.SbiStudentAreaCode,
			&user.SbiStudentAreaCodeAll,
			&user.SbiStudentAreaCodeName,
			&user.SbiHobbies,
			&user.SbiFamilyAddress,
			&user.SbiFamilyAddressName,
			&user.SbiDetailAddress,
			&user.SbiPostCode,
			&user.SbiHomePhone,
			&user.SbiEnrollmentDate,
			&user.SbiGraduationDate,
			&user.SbiMidSchoolAddress,
			&user.SbiMidSchoolAddressName,
			&user.SbiMidSchoolName,
			&user.SbiCollegeEntranceExamScores,
			&user.SbiAdmissionYear,
			&user.SbiAdmissionYearName,
			&user.SbiForeignLanguageCode,
			&user.SbiForeignLanguageCodeName,
			&user.SbiStudentOrigin,
			&user.SbiStudentOriginName,
			&user.ClClassCode,
			&user.ClClassName,
			&user.ClResearchArea,
			&user.ClTrainingMode,
			&user.ClTrainingModeName,
			&user.ClProgramLength,
			&user.ClProgramLengthName,
			&user.ClStudentType,
			&user.ClStudentTypeName,
			&user.ClCredentialsType,
			&user.ClCredentialsTypeName,
			&user.ClDegreeType,
			&user.ClDegreeTypeName,
			&user.ClCounselor,
			&user.ClCounselorName,
			&user.ClAdviser,
			&user.ClAdviserName,
			&user.ClLeadership,
			&user.ClLeadershipName,
			&user.ClSupervisor,
			&user.ClSupervisorName,
			&user.CpCampusName,
			&user.CpCampusCode,
			&user.GdGradeName,
			&user.GdGradeCode,
			&user.AcAcademyName,
			&user.AcAcademyCode,
			&user.MjMajorName,
			&user.MjMajorCode)
		if scanErr != nil {
			//			panic("数据查询出错！")
			panic(scanErr.Error())
			return
		}
		user.University = school
		users = append(users, &user)
		return
	})

	return
}
